import logging
from datetime import datetime

from sqlalchemy import text


def getOrder(db,tableName:str=None,filed:str=None,prefix:str=None,num:int=3,):
    """
    自动生成单号：规则前缀+年月日+流水
    session = get_yun_session(dbData['ERP'])
    order,err = getOrder(db=session,tableName="MF_MM0",filed='MM_NO',prefix="MM",num=3)
    if err !=None:
        print('异常:' + err)
    else:
        print('单号:' + order)
    :param tableName: 表名称
    :param filed: 字段名称
    :param prefix: 单号前缀
    :param num: 流水为的长度
    :return: order
    """
    if tableName == None:
        return None,'表名不能为空'
    if filed == None:
        return None,'字段名不能为空'
    if prefix == None:
        return None,'字段名不能为空'
    # 获取单号长度（8为年月日总计长度为八位）
    # orderLen = 前缀长度 + 年月日 + 流水位数
    orderLen = len(prefix) + 8 + num
    # 获取当前日期和时间
    current_datetime = datetime.now()
    # 使用strftime方法格式化日期
    formatted_date = current_datetime.strftime("%Y%m%d")
    sqlFormat = {
        "tableName":tableName,
        "filed":filed,
        "prefixLen":len(prefix)+1,
        "formatted_date":formatted_date,
        "orderLen":orderLen,
        "prefixIdLen":len(prefix),
        "prefix":prefix
    }
    sql = "select max({filed}) as max_order from {tableName} where SUBSTRING({filed}, {prefixLen}, 8)='{formatted_date}' and len({filed})={orderLen} and SUBSTRING({filed}, 1, {prefixIdLen})='{prefix}'".format(**sqlFormat)
    # sql = "select max(MM_NO) as max_order from MF_MM0"
    # print(sql)
    try:
        rest = db.execute(text(sql), {})
        data = rest.fetchone() #查询单个
        # data = rest.fetchall() #查询列表
        if data[0] == None:
            order = prefix + formatted_date + "{:0{}}".format(1, num)
            return order,None
        else:
            max_order = data[0]
            suffix = int(max_order[-num:])+1
            order = prefix + formatted_date + "{:0{}}".format(suffix, num)
            return order, None
    except Exception as e:
        logging.info('获取单号语句执行失败' + repr(e) + '语句' + sql)
        return None,repr(e)


